﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Web.Script.Serialization;
using System.Windows.Forms;

using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace DataExtract
{
    public partial class Form1 : Form
    {
        public JavaScriptSerializer jss = new JavaScriptSerializer();
        public string path = "";//文件路径

        public List<Student> result = new List<Student>();
        public string[] xlsColumn;
        public Form1()
        {
            InitializeComponent();
            initExcelColumnTag();//初始化excel列名称数组
        }

        public void  initExcelColumnTag()
        {
            string[] str = new string[78];//創建excel列的第一行標識數組
            for (int i = 0; i < 26; i++)
            {

                char temp;
                temp = (char)(i + 65);
                str[i] = temp + "1";

            }
            for (int i = 26; i < 52; i++)
            {

                char temp;
                temp = (char)(i + 65 - 26);
                str[i] = "A" + temp + "1";

            }
            for (int i = 52; i < 78; i++)
            {

                char temp;
                temp = (char)(i + 65 - 52);
                str[i] = "B" + temp + "1";

            }
            xlsColumn = str;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
            fd.Title = "选择文件";//选择框名称
            fd.Filter = "excel files (*.xls)|*.xls;*.xlsx";//选择文件的类型Xls

            if (fd.ShowDialog() == DialogResult.OK)//文件路径
            {
                path = fd.FileName.Trim();
                path = path.Replace("\\", "/");
            }
            textBox1.Text = path;
            
        }

        public DataTable LoadDataExcel(string path)
        {
            
            DataTable dt = new DataTable("Sheet1");

            using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook;
                if (Path.GetExtension(path).ToLower() == ".xlsx")
                    workbook = new XSSFWorkbook(fs); // 处理.xlsx文件
                else
                    workbook = new HSSFWorkbook(fs); // 处理.xls文件

                // 获取指定工作表（名称需与原有逻辑一致）
                ISheet sheet = workbook.GetSheet("学生考勤月报") ?? throw new Exception("未找到工作表[学生考勤月报]");

                // 初始化DataTable列（动态生成）
                IRow headerRow = sheet.GetRow(3); // 假设第3行是列头（原代码从i=3开始读数据）,姓名，班级，日期行


                // 创建DataTable列（与原逻辑匹配）
                
                foreach (ICell cell in headerRow.Cells)
                {
                    dt.Columns.Add("colm"+cell.ColumnIndex);
                }
                

                // 读取数据行（从第4行开始，对应原代码i=3）
                for (int rowIndex = 2; rowIndex <= sheet.LastRowNum; rowIndex++)
                {
                    IRow currentRow = sheet.GetRow(rowIndex);
                    if (currentRow == null) continue;

                    DataRow dr = dt.NewRow();
                    bool hasValue = false;

                    for (int colIndex = 0; colIndex < headerRow.Cells.Count; colIndex++)
                    {
                        ICell cell = currentRow.GetCell(colIndex);
                        string cellValue = GetCellValue(cell);

                        dr[colIndex] = cellValue;
                        if (colIndex == 0 && !string.IsNullOrEmpty(cellValue))
                            hasValue = true; // 检查"姓名"列是否为空（原逻辑）
                    }

                    if (hasValue) dt.Rows.Add(dr); // 跳过姓名为空的行
                }
            }

            return dt;
        }

        // 辅助方法：获取单元格值的字符串表示
        private string GetCellValue(ICell cell)
        {
            if (cell == null) return "";
            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Numeric:
                    return DateUtil.IsCellDateFormatted(cell)
                        ? cell.DateCellValue.ToString()
                        : cell.NumericCellValue.ToString();
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                //case CellType.Formula:
                //    return GetCellValue(cell, cell.CachedFormulaResultType);
                default:
                    return "";
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            richTextBox1.Clear();
            result.Clear();

            int i = 3;
            string temp = null;
            System.Data.DataTable dt = new System.Data.DataTable();

            //temp = ds.Tables[0].Rows[1].ItemArray[1].ToString();
            dt = LoadDataExcel(path);

            DataRow weekDays = dt.Rows[0];//每天星期几
            DataRow dateArr = dt.Rows[1];//获取日期
            
            string[] filter = { "✔", "{}", "" };

            for (i = 2; i < dt.Rows.Count; i++)
            {
                Student tempStud = new Student();

                if (dt.Rows[i][0].ToString() == "")//名字字段为空时跳过循环
                {
                    continue;
                }

                tempStud.stName = dt.Rows[i][0].ToString();

                tempStud.grade = dt.Rows[i][1].ToString();
                List<string> dtList = new List<string>();
                List<string> reasonList = new List<string>();
                for (int j = 2; j < dt.Columns.Count; j++)
                {

                    string cellCent = dt.Rows[i][j].ToString();


                    if (cellCent == "")//周六或周日为空，万一调班，周六日也可能不为空
                    {
                        continue;
                    }


                    dtList.Add(dateArr[j].ToString());
                    reasonList.Add(dt.Rows[i][j].ToString());

                }
                tempStud.absentDate = dtList;
                tempStud.absentReason = reasonList;

                result.Add(tempStud);
            }


            foreach (Student st in result)
            {
                List<List<string>> resultList = new List<List<string>>();//保存每段連續的假期
                List<string> sectionList = new List<string>();
                int continualTag = 0;
                string lastReason = "";
                for (int k = 0; k < st.absentDate.Count; k++)
                {
                    string curReason = st.absentReason[k];

                    if (!curReason.Contains("缺勤") && !curReason.Contains("假"))
                    {
                        if (sectionList.Count > 1)
                        {
                            resultList.Add(sectionList.GetRange(0, sectionList.Count));

                        }
                        sectionList.Clear();

                        continue;
                    }
                    sectionList.Add(st.absentDate[k]);

                    if (k == st.absentDate.Count - 1 && sectionList.Count > 1)
                    {
                        resultList.Add(sectionList.GetRange(0, sectionList.Count));
                    }
                }
                st.judgeInfo = resultList;
                richTextBox1.AppendText(st.stName + " " + jss.Serialize(st.judgeInfo) + "\n");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (result.Count == 0)
                return;

            try
            {
                // 创建NPOI工作簿（HSSF用于.xls，XSSF用于.xlsx）
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Sheet1");

                // 创建标题行
                IRow headerRow = sheet.CreateRow(0);
                headerRow.CreateCell(0).SetCellValue("姓名");
                headerRow.CreateCell(1).SetCellValue("班级");
                headerRow.CreateCell(2).SetCellValue("请假期间");
                headerRow.CreateCell(3).SetCellValue("实际请假天数");
                headerRow.CreateCell(4).SetCellValue("伙食费退款天数");

                // 填充数据
                int rowIndex = 1;
                foreach (Student st in result)
                {
                    if (st.judgeInfo.Count == 0) continue;

                    IRow dataRow = sheet.CreateRow(rowIndex);
                    dataRow.CreateCell(0).SetCellValue(st.stName);   // 姓名
                    dataRow.CreateCell(1).SetCellValue(st.grade);    // 班级

                    // 拼接请假详情并计算天数
                    StringBuilder absenceDetail = new StringBuilder();
                    int totalDays = 0, refundDays = 0;
                    foreach (var period in st.judgeInfo)
                    {
                        int days = period.Count;
                        string startDate = formatDate(period.First());
                        string endDate = formatDate(period.Last());
                        absenceDetail.Append($" {startDate}-{endDate} ({days}-{days - 1})");
                        totalDays += days;
                        refundDays += (days - 1);
                    }

                    dataRow.CreateCell(2).SetCellValue(absenceDetail.ToString()); // 请假期间
                    dataRow.CreateCell(3).SetCellValue(totalDays);               // 实际请假天数
                    dataRow.CreateCell(4).SetCellValue(refundDays);              // 伙食费退款天数

                    rowIndex++;
                }

                // 自动调整列宽（遍历所有列）
                for (int i = 0; i < 5; i++)
                {
                    sheet.AutoSizeColumn(i);
                    // 手动调整列宽上限（可选）
                    if (sheet.GetColumnWidth(i) > 8000) sheet.SetColumnWidth(i, 8000);
                }

                // 保存文件
                using (SaveFileDialog saveDialog = new SaveFileDialog())
                {
                    saveDialog.Filter = "Excel 97-2003文件|*.xls|Excel文件|*.xlsx";
                    if (saveDialog.ShowDialog() == DialogResult.OK)
                    {
                        using (FileStream fs = new FileStream(saveDialog.FileName, FileMode.Create))
                        {
                            workbook.Write(fs);
                        }
                        MessageBox.Show("导出成功！");
                    }
                }
            }
            catch (Exception ex)
            {
                richTextBox1.AppendText($"导出失败: {ex.Message}\n");
            }
        }
        private string formatDate(string dtStr)
        {
            DateTime result ;

            try {
                DateTime.TryParse(dtStr, out result);
                return result.ToString("M.d");
            }
            catch(Exception e)
            {
            }
            return "";
        }
    }
}
